﻿Imports System.Data.OleDb
Public Class UsersDAO
    Shared conn_str As String = My.Settings.ConnectionString
    Shared Function GetAll() As DataTable
        Dim dt As New DataTable
        Dim query As String = "select * from [users]"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function IsExists(ByVal obj As User) As Boolean
        Dim dt As New DataTable
        Dim query As String = "select count(*) from [users] where username = ?"
        Try
            Dim conn As New OleDbConnection(conn_str)
            Dim cmd As New OleDbCommand(query, conn)
            cmd.Parameters.Add("@username", OleDbType.VarChar, 50).Value = obj.username
            conn.Open()
            Return Integer.Parse(cmd.ExecuteScalar()) > 0
            conn.Close()
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return False
    End Function
    Shared Function CheckUser(ByVal username As String, ByVal password As String) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select * from [users] where username = ? and password = ?"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.AddWithValue("@username", username)
            adapter.SelectCommand.Parameters.AddWithValue("@password", password)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function Them(ByRef dt As DataTable, obj As User) As Boolean        
        Dim query As String = "select * from [users]"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            Dim b As New OleDbCommandBuilder(adapter)            
            Dim dr As DataRow = dt.NewRow()
            dr("fullname") = obj.Fullname
            dr("username") = obj.username
            dr("QuyenHan") = obj.QuyenHan
            dr("password") = obj.Password
            dt.Rows.Add(dr)
            adapter.Update(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
            Return False
        End Try
        Return True
    End Function
    Shared Function Sua(ByRef dt As DataTable, obj As User) As Boolean
        Dim query As String = "select * from [users]"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            Dim b As New OleDbCommandBuilder(adapter)
            Dim dr As DataRow = dt.Rows.Find(obj.MaUser)
            dr("fullname") = obj.Fullname
            dr("username") = obj.username
            dr("QuyenHan") = obj.QuyenHan
            dr("password") = obj.Password
            adapter.Update(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
            Return False
        End Try
        Return True
    End Function
End Class
